var { pool } = require('../utils/dbUtil')

var moment = require("moment")

// 查找admin
function selectAllGoods(obj) {
    let { aTypeId, bTypeId, goodName, page, num } = obj
    // console.log(obj)
    // 分页sql
    let sql = `SELECT atype.typeName 'aTypeName',btype.typeName 'bTypeName',good.* FROM atype INNER JOIN btype ON atype.aTypeId = btype.aTypeId and atype.aTypeId LIKE concat('%',?,'%') and btype.bTypeId LIKE concat('%',?,'%') INNER JOIN good ON btype.bTypeId = good.bTypeId and btype.aTypeId = good.aTypeId and good.goodName LIKE concat('%',?,'%') order by goodOrder,goodTime LIMIT ${(page - 1) * num},${num}`
    // 非分页sql
    let sql2 = `SELECT count(*) 'sum' FROM atype INNER JOIN btype ON atype.aTypeId = btype.aTypeId and atype.aTypeId LIKE concat('%',?,'%') and btype.bTypeId LIKE concat('%',?,'%') INNER JOIN good ON btype.bTypeId = good.bTypeId and btype.aTypeId = good.aTypeId and good.goodName LIKE concat('%',?,'%') order by goodOrder,goodTime`
    return new Promise((resolve, reject) => {
        pool.getConnection(function (connError, connection) {
            if (connError) {
                reject(connError)
            } else {
                let q = connection.query(sql, [aTypeId, bTypeId, goodName, page, num], (queryError, rows) => {
                    if (queryError) {
                        connection.release()
                        reject(queryError)
                    } else {
                        // console.log(q.sql)
                        connection.query(sql2, [aTypeId, bTypeId, goodName, page, num], (queryError2, row2) => {
                            if (queryError2) {
                                connection.release()
                                reject(queryError2)
                            } else {
                                connection.release()
                                let { sum } = row2[0]
                                resolve({ rows, sum })
                            }
                        })
                    }
                })

            }
        })
    })
}
// 删除商品
function delGoods(arr) {
    let sql = `delete from good where aTypeId=? and bTypeId=? and goodId=?`;
    return new Promise((resolve, reject) => {
        pool.getConnection(function (connError, connection) {
            if (connError) {
                reject(connError)
            } else {
                connection.beginTransaction(err => {
                    if (err) {
                        connection.release()
                        reject(err)
                    } else {
                        for (let i = 0; i < arr.length; i++) {
                            let { aTypeId, bTypeId, goodId } = arr[i];
                            connection.query(sql, [aTypeId, bTypeId, goodId], (queryError, row) => {
                                if (queryError) {
                                    connection.release()
                                    reject(queryError)
                                }
                                if (i == arr.length - 1) {
                                    connection.commit(error => {
                                        if (error) {
                                            connection.release()
                                            reject(error)
                                        } else {
                                            connection.release()
                                            resolve(row)
                                        }
                                    })
                                }
                            })
                        }
                    }
                })
            }
        })
    })
}
// 插入商品
function insertGoods(obj) {
    let { aTypeId, bTypeId, goodName, goodBrand, goodOrder, goodPrice, goodPriceCheap, goodIntro, goodPicSmall, goodPicLarge } = obj
    // let sql = "insert into good(goodId,aTypeId,bTypeId,goodTypeCode,goodCode,goodName,goodIntro,goodPicSmall,goodPicLarge,goodPrice,goodPriceCheap,goodBrand,goodTime,goodOrder,goodHits,goodReviews,goodOrderNum,goodNum) "
    let sql = "insert into good " +
        "(goodId,aTypeId,bTypeId,goodName,goodIntro,goodPicSmall,goodPicLarge, " +
        "goodPrice,goodPriceCheap,goodBrand,goodTime,goodOrder,goodHits,goodReviews,goodOrderNum,goodNum) " +
        "values(?,?,?,?,?,?,?,?,?,?,STR_TO_DATE(?,'%Y-%m-%d %T'),?,?,?,?,?)"
    return new Promise((resolve, reject) => {
        pool.getConnection(function (connError, connection) {
            if (connError) {
                reject(connError)
            } else {
                let date = new Date()
                let timestamp = date.getTime();
                let time = moment(date).format("YYYY-MM-DD HH:mm:SS")
                connection.query(sql, ["g" + timestamp, aTypeId, bTypeId, goodName, goodIntro, goodPicSmall, goodPicLarge, goodPrice, goodPriceCheap, goodBrand, time, goodOrder, 0, 0, 0, 0], (queryError, rows) => {
                    if (queryError) {
                        connection.release()
                        reject(queryError)
                    } else {
                        connection.release()
                        resolve(rows)
                    }
                })
            }
        })
    })
}
/*
SELECT 
    atype.typeName 'aTypeName',
    btype.typeName 'bTypeName',
    good.* 
FROM atype INNER JOIN btype 
ON 
    atype.aTypeId = btype.aTypeId 
    and atype.aTypeId LIKE concat('%','','%')
    and btype.bTypeId LIKE concat('%','','%')
INNER JOIN good 
ON 
    btype.bTypeId = good.bTypeId 
    and btype.aTypeId = good.aTypeId 
    and good.goodName LIKE concat('%','','%') 
order by goodOrder,goodTime
*/
module.exports = {
    selectAllGoods,
    delGoods,
    insertGoods
}